package cn.tnar.parkservice.mapper;

import cn.tnar.parkservice.config.query.QuerySupport;
import cn.tnar.parkservice.model.dto.OrderAmountDto;
import cn.tnar.parkservice.model.dto.TimeDto;
import cn.tnar.parkservice.model.entity.TParkSerialnoReportLog;
import cn.tnar.parkservice.model.request.CurrentViewRequset;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author clarck
 * @since 2018-09-17
 */
@Mapper
@Repository
@QuerySupport
public interface TParkSerialnoReportLogMapper extends BaseMapper<TParkSerialnoReportLog> {

    /**
     * 趋势总览  进出场流量
     *
     * @param ctype
     * @param startTime
     * @param endTime
     * @param parkCode
     * @return
     */
    List<TimeDto> queryTodayAcountData(@Param("ctype") int ctype, @Param("startTime") long startTime, @Param("endTime") long endTime, @Param("parkCode") String parkCode);

    /**
     * 趋势总览 实际收入
     *
     * @param parkCode
     * @param startTime
     * @param endTime
     * @return
     */
    public List<OrderAmountDto> queryTodayAmtData(@Param("parkCode") String parkCode, @Param("startTime") long startTime, @Param("endTime") long endTime);

    /**
     * 流量构成
     *
     * @param park_code
     * @return
     */
    @Select("SELECT " +
            "((SELECT count(*) FROM t_park_day_parking_record WHERE payflag = 1 and parkamt = 0.00 and park_code in (${park_code})) +" +
            " (SELECT count(*) FROM t_park_his_parking_record WHERE payflag = 1 and parkamt = 0.00 and park_code in (${park_code}))) as pass," +
            " SUM(monthcard_count) as monthcard," +
            " SUM(self_weixin_count+self_zfb_count + etc_pay_count + cloud_juhe_count + escape_juhe_count ) as balance, " +
            " SUM(charge_count) as cash," +
            " SUM(charge_count+self_weixin_count+self_zfb_count + etc_pay_count + cloud_juhe_count + escape_juhe_count ) as total" +
            " FROM t_park_serialno_report_log  " +
            " where park_code in (${park_code}) ")
    public Map<String, Object> statisGroupByFlow(@Param("park_code") String park_code);


    /**
     * 收入构成
     *
     * @param park_code
     * @return
     */
//    @Select("SELECT " +
//            "SUM(self_weixin_account+self_zhifubao_account+balance_actual_account) as elecPay," +
//            " SUM(total_actual_account) as cash," +
//
//            " SUM(elec_coupon_amt) AS coupon"+
//            "  FROM t_park_serialno_report_log  " +
//            " where park_code in (${park_code}) ")
    @Select("select * from(" +
            "(SELECT\n" +
            "coupond + couponh AS coupon " +
            "FROM (" +
            "( SELECT IFNULL( sum( offline_coupon_amt ) + sum( elec_coupon_amt ), 0 ) AS coupond FROM t_park_day_parking_record WHERE park_code IN ( ${park_code} ) ) AS tpdpr," +
            "( SELECT IFNULL( sum( offline_coupon_amt ) + sum( elec_coupon_amt ), 0 ) AS couponh FROM t_park_his_parking_record WHERE park_code IN ( ${park_code} ) ) AS tphpr " +
            ") ) as a," +
            "(SELECT sum(tradeamount)  AS elecPay FROM `t_trade_commodity_order`   WHERE delivery_area in (${park_code}) ) as c," +
//            "(SELECT SUM(self_weixin_account+self_zhifubao_account+balance_actual_account+cloud_juhe_amt+escape_juhe_amt+etc_pay_amt+monthcard_amt) as elecPay," +
            "( SELECT SUM(cash) as cash from(  SELECT " +
            " case when charge_count=0 then 0 else total_actual_account end  as cash" +
            "  FROM t_park_serialno_report_log where park_code in (${park_code}) )as d) as b)")
    public Map<String, Object> statisGroupByPayType(@Param("park_code") String park_code);

    /**
     * 交易渠道
     *
     * @param park_code
     * @return
     */
    @Select("SELECT SUM( self_weixin_count) as weixin,SUM( self_zfb_count) as alipay,SUM( balance_actual_account+yu_epay_count) as app," +
            " SUM( self_weixin_count+self_zfb_count+balance_actual_account+yu_epay_count+cloud_juhe_count+escape_juhe_count) as total," +
            " SUM( cloud_juhe_count+escape_juhe_count) as togetherPay" +
            " from t_park_serialno_report_log" +
            " where 1=1" +
            "  AND park_code in (${park_code}) ")
    public Map<String, Object> statisGroupByPayChannels(@Param("park_code") String park_code);

    /**
     * 实时总览 - 今日电子支付渠道分析
     *
     * @param req
     * @return
     */
    // 新增一个parkCode
//    @Select("SELECT " +
//            " SUM( self_weixin_account) AS weixin, " +
//            " SUM( self_zhifubao_account) AS alipay, " +
//            " SUM( balance_actual_account + yu_epay_amt) AS app, " +
//            " SUM(escape_repay_amt) AS escape_repay_amt, " +
//            " SUM(escape_cash_repay_amt) AS escape_cash_repay_amt, " +
//            " SUM(escape_amt) AS escape_amt, " +
//            " SUM( self_weixin_account+self_zhifubao_account+balance_actual_account+ yu_epay_amt+escape_repay_amt) AS total " +
//            " FROM t_park_serialno_report_log" +
//            " WHERE park_code in (${req.parkCode}) and occur_date BETWEEN #{req.startTime} AND  #{req.endTime}")
    @Select("select  sum(tradeamount) as total, " +
            "ifnull(sum(case when pay_type = 1 then tradeamount else 0.00 end) ,0.00) as weixin," +
            "ifnull(sum(case when pay_type = 13 then tradeamount else 0.00 end) ,0.00) as app, " +
            "ifnull(sum(case when pay_type = 2 then tradeamount else 0.00 end) ,0.00) as alipay, " +
            "ifnull(sum(case when pay_type = 10 then tradeamount else 0.00 end) ,0.00) as togetherPay, " +
            "ifnull(sum(case when pay_type != 1 and pay_type != 2  and pay_type != 10 and pay_type != 13 then tradeamount else 0.00 end) ,0.00) as total_other_amount " +
            "from t_trade_commodity_order " +
            "where  delflag=1 and commprovider in (select cust_id FROM  `t_park_info` where park_code in (${req.parkCode})) and ordertime BETWEEN #{req.startTime} AND  #{req.endTime} ")
    Map<String, Object> statisGroupByPayChannel(@Param("req") CurrentViewRequset req);


    /**
     * 查询今天每个小时 路内/路外 进场/出场 流量
     *
     * @param ctype     进出场状态 1：进场  2：出场
     * @param ptype     停车场状态 1：路内  2.路外
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("<script>" +
            " select count(*) as num," +
            " <when test='ctype==1'>SUBSTR(intime,9,2)</when> " +
            " <when test='ctype==2'>SUBSTR(outtime,9,2)</when> " +
            " as time FROM t_park_day_parking_record" +
            " WHERE 1=1 and outoperate &lt;&gt;6 " +
            " <when test='ctype!=2'> AND  intime BETWEEN #{startTime} AND #{endTime}</when>" +
            " <when test='ctype!=1'> AND  outtime BETWEEN #{startTime} AND #{endTime}</when>" +
            " AND park_code IN (SELECT park_code FROM t_park_info WHERE ptype=#{ptype} AND delflag =1 AND park_code IN (${parkCode})) GROUP BY time" +
            "</script>")
    List<TimeDto> queryParkingAnalysisByToday(@Param("ctype") int ctype, @Param("ptype") int ptype, @Param("startTime") long startTime, @Param("endTime") long endTime, @Param("parkCode") String parkCode);

    /**
     * 查询一天每个小时 路内/路外 进场/出场 流量
     *
     * @param ctype     进出场状态 1：进场  2：出场
     * @param ptype     停车场状态 1：路内  2.路外
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("<script>" +
            " select count(*) as num," +
            " <when test='ctype==1'>SUBSTR(intime,9,2)</when> " +
            " <when test='ctype==2'>SUBSTR(outtime,9,2)</when> " +
            " as time FROM t_park_his_parking_record" +
            " WHERE 1=1 and outoperate &lt;&gt;6" +
            " <when test='ctype==1'> AND  intime BETWEEN #{startTime} AND #{endTime}</when>" +
            " <when test='ctype==2'> AND  outtime BETWEEN #{startTime} AND #{endTime}</when>" +
            " AND park_code IN (SELECT park_code FROM t_park_info WHERE ptype=#{ptype} AND delflag =1 AND park_code IN (${parkCode})) GROUP BY time" +
            "</script>")
    List<TimeDto> queryParkingAnalysisByDay(@Param("ctype") int ctype, @Param("ptype") int ptype, @Param("startTime") long startTime, @Param("endTime") long endTime, @Param("parkCode") String parkCode);

    /**
     * 查询一月每一天 路内/路外 进场/出场 流量
     *
     * @param ctype     进出场状态 1：进场  2：出场
     * @param ptype     停车场状态 1：路内  2.路外
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("<script>" +
            "select count(*) as num," +
            " <when test='ctype==1'>SUBSTR(intime,7,2)</when> " +
            " <when test='ctype==2'>SUBSTR(outtime,7,2)</when> " +
            " as time FROM t_park_his_parking_record" +
            " WHERE 1=1 and outoperate &lt;&gt;6" +
            " <when test='ctype==1'> AND  intime BETWEEN #{startTime} AND #{endTime}</when>" +
            " <when test='ctype==2'> AND  outtime BETWEEN #{startTime} AND #{endTime}</when>" +
            " AND park_code IN (SELECT park_code FROM t_park_info WHERE ptype=#{ptype} AND delflag =1 AND park_code IN (${parkCode})) GROUP BY time" +
            "</script>")
    List<TimeDto> queryParkingAnalysisByMonth(@Param("ctype") int ctype, @Param("ptype") int ptype, @Param("startTime") long startTime, @Param("endTime") long endTime, @Param("parkCode") String parkCode);

    /**
     * 查询一年每个月 路内/路外 进场/出场 流量
     *
     * @param ctype     进出场状态 1：进场  2：出场
     * @param ptype     停车场状态 1：路内  2.路外
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("<script>" +
            "select count(*) as num," +
            " <when test='ctype==1'>SUBSTR(intime,5,2)</when> " +
            " <when test='ctype==2'>SUBSTR(outtime,5,2)</when> " +
            " as time FROM t_park_his_parking_record" +
            " WHERE 1=1 and outoperate &lt;&gt;6" +
            " <when test='ctype==1'> AND  intime BETWEEN #{startTime} AND #{endTime}</when>" +
            " <when test='ctype==2'> AND  outtime BETWEEN #{startTime} AND #{endTime}</when>" +
            " AND park_code IN (SELECT park_code FROM t_park_info WHERE ptype=#{ptype} AND delflag =1 AND park_code IN (${parkCode})) GROUP BY time" +
            "</script>")
    List<TimeDto> queryParkingAnalysisByYear(@Param("ctype") int ctype, @Param("ptype") int ptype, @Param("startTime") long startTime, @Param("endTime") long endTime, @Param("parkCode") String parkCode);

    /**
     * 查询路内路外停车场信息
     *
     * @param ptype
     * @return
     */
    @Select("select id,cust_id,park_code,name,region_code,business_code,capacity_desc,capacity_num,free_num," +
            "member_num,charge_num,lng,lat,feeindex,feeamt,feedesc,freetime,feelevel,user_id," +
            "utime,ptype,subtype,status,addtime,jointime,address,remark,delflag,seatarea,appointflag," +
            "uploadtime,feeRate,mtime  FROM t_park_info where ptype = #{ptype} AND delflag =1")
    List<Map<String, Object>> queryParkByPtype(int ptype);

    /**
     * 查询路内路外运营商下停车场信息
     *
     * @param ptype custId
     * @return
     */
    @Select("select id,cust_id,park_code,name,region_code,business_code,capacity_desc,capacity_num,free_num," +
            "member_num,charge_num,lng,lat,feeindex,feeamt,feedesc,freetime,feelevel,user_id," +
            "utime,ptype,subtype,status,addtime,jointime,address,remark,delflag,seatarea,appointflag," +
            "uploadtime,feeRate,mtime  FROM t_park_info where ptype = #{ptype} AND delflag =1 and cust_id in (SELECT cust_id from t_acct_cust_relation WHERE cust_parent_id=#{custId})")
    List<Map<String, Object>> queryParkByPtypeCustId(@Param("ptype") int ptype, @Param("custId") String custId);

    @Select("<script>" +
            " select count(*) " +
            " FROM t_park_day_parking_record" +
            " WHERE 1=1 and outoperate &lt;&gt;6" +
            " <when test='ctype!=2'> AND  intime >= #{todayStart} </when>" +
            " <when test='ctype!=1'> AND  outtime >= #{todayStart} </when>" +
            " AND park_code IN (SELECT park_code FROM t_park_info WHERE ptype=#{ptype} AND delflag =1) " +
            "</script>")
    Integer countToday(@Param("ctype") int ctype, @Param("ptype") int ptype, @Param("todayStart") long todayStart);

    @Select("SELECT sum(memberinof.amount_num) FROM t_car_memberamountinfo memberinof JOIN t_car_feesroleinfo feeinfo " +
            " on memberinof.fees_roleid=feeinfo.id  where feeinfo.card_type=1  and memberinof.park_code IN (${park_code})")
    String getmonthValue(@Param("park_code") String parkCode);
}
